/*! @file sqlitedb.cpp
 * @brief implements the SqliteDb Class
 */

#include "defines.h"
#include "sqlitedb.h"
#include "sqlite3.h"

#include <QFileInfo>
#include <QDebug>

/*! @brief Konstruktor
 * @callgraph
 * @callergraph
 *
 * @param dbfile QString path to dbfile
 */
SqliteDb::SqliteDb( QString dbfile )
{
    counter = 0;
    longpath = dbfile;
    _instantiateDb();
}

/** @brief SqliteDb Destructor
 * @callgraph
 * @callergraph
 */
SqliteDb::~SqliteDb()
{
    counter = 0;
    sqlite3_close( db );
}

/*! @brief appends a table to db
 * @callgraph
 * @callergraph
 *
 * @param t QList<QString>
 */
void SqliteDb::addTable( QString t )
{
    tables.append( t );
}

/*! @brief returns first and last entry date
 * @callgraph
 * @callergraph
 *
 * @return QList<QDate> Dates
 */
QList<QDate> SqliteDb::getDates()
{
    Dates << first_entry << last_entry;
    return Dates;
}

QList<QString> SqliteDb::getFields(int i)
{
    //qDebug() << "SqliteDb\t| getFields for idx:" << i;
    //qDebug() << "SqliteDb\t| fields.at(i):" << fields.at(i);
    return fields.at(i);
}

/*! @brief returns filename
 * @callgraph
 * @callergraph
 *
 * @return QString filename
 */
QString SqliteDb::getFileName()
{
    return filename;
}

/*! @brief returns type of db as integer
 * @callgraph
 * @callergraph
 *
 * @return dbtype int value of dbtype
 */
int SqliteDb::getIntType()
{
    if (dbtype == "places.sqlite")
        return FileType::FT_FIREFOX_PLACES;
    else if ( dbtype == "signons.sqlite")
        return FileType::FT_FIREFOX_SIGNONS;
    else if (dbtype == "mmssms.db")
        return FileType::FT_ANDROID_MMS;
    else if (dbtype == "other")
        return FileType::FT_OTHER;
    else if (dbtype == "empty")
        return FileType::FT_EMPTY;
    else if (dbtype == "notable")
        return FileType::FT_NOTABLE;
    else if (dbtype == "invalid")
        return FileType::FT_INVALID;
    else
        return FileType::FT_INVALID;
}

/** @brief return path including filename
 * @callgraph
 * @callergraph
 */
QString SqliteDb::getLongPath()
{
    return longpath;
}

/*! @brief returns path w/o filename
 * @callgraph
 * @callergraph
 *
 * @return QString path
 */
QString SqliteDb::getPath()
{
    return path;
}

/** @brief returns db size in mb
 * @callgraph
 * @callergraph
 *
 * @return double size in mb
 */
double SqliteDb::getSizeMb()
{
    // TODO: return MB or KB
    QFileInfo *info = new QFileInfo(this->longpath);

    return info->size()/1048576.0;
}

/*! @brief returns the tables in db
 * @callgraph
 * @callergraph
 *
 * @return QList<QString> tables
 */
QList<QString> SqliteDb::getTables()
{
    return tables;
}

/*! @brief returns type of db as QString
 * @callgraph
 * @callergraph
 *
 * @return QString dbtype
 */
QString SqliteDb::getType()
{
    return dbtype;
}

/*! @brief sets tables in db
 * @callgraph
 * @callergraph
 *
 * @param t QList<QString>
 */
void SqliteDb::setTables( QList<QString> t)
{
    tables = t;
}

bool SqliteDb::isTableEmpty(QString table) {
    char **results = NULL;
    int rows=0, columns=0;
    char *error;

    QString sqlTree = "SELECT * FROM " + table + " LIMIT 1;";
    int rc = sqlite3_get_table(handle, sqlTree.toLatin1(), &results, &rows, &columns, &error);
    if(rc) {
        sqlite3_free_table(results);
        sqlite3_free(error);
        return false;       // if error don't change anything
    } else {
        // NOTE: if rows == 0 set to empty
        if (rows == 0)
            return true;
        else
            return false;
    }

}

/*! @brief sets first_entry and last_entry
 * @callgraph
 * @callergraph
 *
 * @param *tmp sqlite3 handle to db
 * @attention modifies first_entry and last_entry
 */
void SqliteDb::_getDates( sqlite3 *tmp )
{
    char **results = NULL;
    int rows, columns;
    char *error;
    const char *sqlDate = NULL;
    QString timeFormat = "yyyy'-'MM'-'dd";

    if( dbtype == "places.sqlite") {
        sqlDate = "SELECT date(moz_historyvisits.visit_date/1000000,'unixepoch') AS Date"
                " FROM moz_historyvisits ORDER BY date ASC;";
    } else if( dbtype == "mmssms.db") {
        sqlDate = "SELECT date(sms.date/1000,'unixepoch') AS Date FROM sms ORDER BY date ASC;";
    }
    // unknown file, set Dates to today
    else if( dbtype == "other"
             || dbtype == "invalid"
             || dbtype == "empty"
             || dbtype == "notable"
             || dbtype == "signons.sqlite") {
        first_entry = QDate::currentDate();
        last_entry = QDate::currentDate();
        return;
    }

    int rc = sqlite3_get_table(tmp, sqlDate, &results, &rows, &columns, &error);

    if(rc || rows == 0) {
        sqlite3_free_table(results);
        sqlite3_free(error);
        first_entry = QDate::currentDate();
        last_entry = QDate::currentDate();
    } else {
        first_entry = QDate::fromString(results[1], timeFormat);
        last_entry = QDate::fromString(results[rows], timeFormat);

        sqlite3_free_table(results);
    }
}

/**
 * @brief
 *
 * @fn SqliteDb::_getFields
 * @param tmp
 * @param table
 * @return QList<QString>
 */
QList<QString> SqliteDb::_getFields(sqlite3 *tmp, QString table)
{
    QList<QString> tmpfields;
    QString cntsql = "SELECT * FROM " + table + ";";    // NOTE: LIMIT 1?
    //qDebug() << "SqliteDb\t|" << filename << "as:" << dbtype
    //         << "SQL: SELECT * FROM " + table + ";"
    //         << "fields id:" << counter++;

    sqlite3_stmt *stmt;
    sqlite3_prepare_v2(tmp, cntsql.toLatin1(), -1, &stmt, 0);
    int cols = sqlite3_column_count(stmt);
    for(int cc=0; cc<cols; cc++) {
        //qDebug() << "SqliteDb\t| cc=" << cc << sqlite3_column_name(stmt, cc);
        tmpfields << QString(sqlite3_column_name(stmt, cc));
    }

//    qDebug() << "SqliteDb\t| tmpfields:" << tmpfields;
    return tmpfields;
}

/*! @brief get the tables from db and fill variable tables
 * @callgraph
 * @callergraph
 *
 * @param tmp sqlite3* handle to db
 * @return QList<QString> tables
 */
QList<QString> SqliteDb::_getTables( sqlite3 *tmp )
{
    char **results = NULL;
    int rows, columns;
    char *error;
    const char *sqlTree;

    sqlTree = "SELECT * FROM (SELECT name FROM sqlite_master WHERE type='table');";

    int rc = sqlite3_get_table(tmp, sqlTree, &results, &rows, &columns, &error);

    if(rc) {
        sqlite3_free_table(results);
        sqlite3_free(error);
        tables << "ERROR";
        return tables;
    } else {
            for( int i=1; i<=rows; i++) {
                tables << QString( results[i]);
                QStringList tmplst = _getFields(tmp, QString( results[i]));
                fields.append(tmplst);
            }
    }

    sqlite3_free_table(results);
    sqlite3_free(error);

    return tables;
}

/*! @brief is it a places.sqlite, Android or 'other' db?
 * @callgraph
 * @callergraph
 *
 * @return QString 'places.sqlite' ||
 * @return QString 'signons.sqlite' ||
 * @return QString 'android' ||
 * @return QString 'other' ||
 * @return QString 'invalid'
 */
QString SqliteDb::_identifyDb( sqlite3 *handle )
{
    char **results = NULL;
    int rows=0, columns=0, i=0;
    char *error;

    const char *sqlTree = "SELECT name FROM sqlite_master WHERE type='table';";
    int rc = sqlite3_get_table(handle, sqlTree, &results, &rows, &columns, &error);
    if(rc) {
        sqlite3_free_table(results);
        sqlite3_free(error);
        return QString("invalid");
    } else {
        // NOTE: if rows == 0 set to noTable
        if (rows == 0)
            return QString("notable");

        for( i=0; i<rows; i++ ) {
            if( QString(results[i]).compare(QString("moz_places")) == 0 )
                return QString("places.sqlite");
            if( QString(results[i]).compare(QString("moz_logins")) == 0)
                return QString("signons.sqlite");
//            else if( QString(results[i]).compare(QString("sms")) == 0)
//                return QString("mmssms.db");
        }
        // otherwise return 'other'
        return QString("other");
    }
}

/*! @brief opens database file 'dbfile' and reads needed values
 * @callgraph
 * @callergraph
 *
 * @return rc int errCode from sqlite3_open_v2
 */
int SqliteDb::_instantiateDb()
{
    // file related
    QFileInfo fileinfo( longpath );
    path = fileinfo.path();
    filename = fileinfo.fileName();

    // database related
    int rc = sqlite3_open_v2(longpath.toUtf8(),
                             &db,
                             SQLITE_OPEN_READONLY|SQLITE_OPEN_URI,
                             0);
    if(rc) {
        sqlite3_close(db);
        dbtype = "invalid";
        return -99;
    } else {
        dbtype = _identifyDb( db );

        // don't proceed if invalid
        if (dbtype == "invalid")
            return -99;
        if (dbtype == "notable")
            return -97;

        handle = db;
        _getTables( db );
        _getDates( db );
        return rc;
    }
}

